package com.red.wood.module.eval.mybatis.service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.red.wood.common.util.CommonUtils;

@Service
public class EvalExcelExport {

	@Resource(name="jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	public String readExcel(Workbook workbook,String fileType,String apply_business) throws IOException{
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			Sheet sheet = workbook.getSheetAt(numSheet);
			String tableName = getTableName(sheet.getSheetName());
			
			String sql = "";
			if(StringUtils.equals(tableName, "t_eval_result_query_master")) {
				sql = "select * from "+tableName+" where apply_business= '"+apply_business+"'";
			}else {
				sql = "select t2.* from t_eval_result_query_master t1,"+tableName+" t2 where t1.evalNum = t2.evalNum and t1.apply_business='"+apply_business+"'";
			}
			list = jdbcTemplate.queryForList(sql);
			
			if (sheet == null || sheet.getRow(1) == null) {
				continue;
			}
			// 循环行Row,第一行为数据中字段，第二行为字段头，第三行开始为数据
			// 获取字段名
			Row rowOne = sheet.getRow(0);//主要是为了获取样式
			Row rowColumn = sheet.getRow(1);// 获取数据库中字段
			Row rowColumnName = sheet.getRow(2);//字段描述
			
			int columns = rowColumnName.getPhysicalNumberOfCells();
			int index = 3;
			Row row = null;
			for(Map<String,Object> map : list) {
				//row = sheet.getRow(index++);
				row = sheet.createRow(index++);
				
				row.setRowStyle(rowOne.getRowStyle());
				for (int i = 0; i < columns; i++) {
					String column = getValue(rowColumn.getCell(i));//字段名称
					Object value = map.get(column);
					Cell cell = row.createCell(i);
					cell.setCellStyle(rowOne.getCell(0).getCellStyle());
					//防止数字变为变成科学计数法，默认都设置为string，若有小数在特殊处理
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					if(value == null) {
						cell.setCellValue("");
					}else {
						//因为有关数值长度大于15位会导致数据丢失，所以所有数值都按文本处理
		                cell.setCellValue(new XSSFRichTextString(value.toString()));
					}
				}
			}
		}
		
		String path =  CommonUtils.getDataTempPath() + "eval_" + System.currentTimeMillis() + ".xlsx";
		File fullPath = new File(path);
		if (fullPath.exists()) {
			fullPath.delete();
		}
		FileOutputStream fOut = new FileOutputStream(fullPath);
		// 把相应的Excel 工作簿存盘
		workbook.write(fOut);
		fOut.flush();
		// 操作结束，关闭文件
		fOut.close();
		workbook.close();
		Map<String, Object> retMap = new HashMap<String, Object>();
		// 返回用户导入的文件名给客户端下载使用
		retMap.put("filePath", path);
		retMap.put("filename", "");
		return path;
	}

	public static String getTableName(String key) {
		Map<String,String> map = new HashMap<String,String>();
		
		map.put("sp主体","t_eval_result_query_master");
		map.put("机房","t_eval_result_query_house");
		map.put("链路","t_eval_result_query_link");
		
		return map.get(key);
	}
	
	public boolean valid(StringBuffer validInfo, Workbook workbook,int columns, Row row, String fileType){
		boolean validFlag = true;
		Cell addCell = null;
		// 如果存在要校验失败信息，则将校验结果追加到源文件中
		if (validInfo.length() > 0) {
			validFlag = false;
			// 生成红色字体样式
			CellStyle style1 = workbook.createCellStyle();
			Font font = workbook.createFont();
			font.setColor(HSSFColor.RED.index);
			font.setFontHeightInPoints((short) 11);
			style1.setFont(font);

			// 在当前行的colNum列上创建单元格
			addCell = row.createCell(columns);
			// 添加内容至单元格
			addCell.setCellValue(validInfo.toString());
			// 设置红色字体
			addCell.setCellStyle(style1);
		}
		return validFlag;
	}
	
	/**
	 * 得到Excel表中的值
	 * @param cell Excel中的每列
	 * @return Excel中每列的值
	 */
	@SuppressWarnings("static-access")
	public String getValue(Cell cell) {
		String value = "";
		if (cell != null) {
			if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
				value = String.valueOf(cell.getBooleanCellValue());// 返回布尔类型的值
			} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
				value = String.valueOf(cell.getNumericCellValue());// 返回数值类型的值
			} else {
				value = String.valueOf(cell.getStringCellValue());// 返回字符串类型的值
			}
		}
		return value;
	}
	
	public static final String mathRandom(final long length) {
		double len = Math.pow(10D, length);
		long result = (long) (len * Math.random());

		// 补齐随机数长度
		while (len / result > 10) {
			result *= 10;
		}
		return Long.toString(result);
	}
}
